﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-03-15
修改:
修改时间: 
功能: 单店毛利率
 ******************************************************/

using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using com.nm.library;
using Nancy.Extensions;

namespace PlanAPI
{
    [Description("单店毛利率")]
    public class PLBasShopGrossRateHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：梁家健
        /// Create Date：2017-03-15
        [MustLogin]
        [Description("单店毛利率列表")]
        [ReturnType(typeof(PaperData<PLBasShopGrossRateModel.ListModel>))]
        public ApiResult GetPLBasShopGrossRateList([ParamDescription("年份")] string Year="",[ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var count = ReturnPLBasShopGrossRateCount(Year);
                var dt = ReturnPLBasShopGrossRateList(Year,Page, Size);
                var result = PaperData<PLBasShopGrossRateModel.ListModel>.Create(count, Size, Page);
                var list = ModelConvertHelper<PLBasShopGrossRateModel.ListModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 单店毛利率列表
        /// </summary>
        /// <param name="parentAreaId"></param>
        /// <param name="page"></param>
        /// <param name="size"></param>
        /// <returns>返回单店毛利率列表</returns>
        /// Writer：梁家健
        /// Create Date：2017-03-15
        public DataTable ReturnPLBasShopGrossRateList(string Year, int Page, int Size)
        {
            try
            {
                var sql = $@"SELECT  *
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY Yearv DESC, rate.SeasonID DESC, rate.KindID, rate.SeriesID ) AS Row
          FROM      ngoods.ngoods_PL_Bas_ShopGrossRate rate
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) kind ON rate.KindID = kind.KindID
                                         AND rate.ExtrSource = kind.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Series 
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) series ON rate.SeriesID = series.SeriesID
                                    AND rate.ExtrSource = series.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Season 
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) season ON rate.SeasonID = season.SeasonID
                                    AND rate.ExtrSource = season.ExtrSource
          WHERE     rate.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND rate.CompanyID = '{UserInfo.UserCompanyId}' 
					{(string.IsNullOrEmpty(Year) ? "" : $@" and rate.Yearv={Year}")}
        ) table1
WHERE   Row BETWEEN ( {Page} - 1 ) * {Size} + 1 AND {Page} * {Size}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 统计单店毛利率总条数
        /// </summary>
        /// <param name="ParentAreaId"></param>
        /// <param name="Keys"></param>
        /// <returns>返回单店毛利率总条数</returns>
        /// Writer：梁家健
        /// Create Date：2017-03-15
        public int ReturnPLBasShopGrossRateCount(string Year)
        {
            try
            {
                var sql = $@"SELECT   COUNT(1)
          FROM      ngoods.ngoods_PL_Bas_ShopGrossRate rate
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Kind
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) kind ON rate.KindID = kind.KindID
                                         AND rate.ExtrSource = kind.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Series 
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) series ON rate.SeriesID = series.SeriesID
                                    AND rate.ExtrSource = series.ExtrSource
                    INNER JOIN ( SELECT *
                                 FROM   nods.nods_erp_SD_Mat_Season 
                                 WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                               ) season ON rate.SeasonID = season.SeasonID
                                    AND rate.ExtrSource = season.ExtrSource
          WHERE     rate.ExtrSource = '{UserInfo.UserExtrSource}'
                    AND rate.CompanyID = '{UserInfo.UserCompanyId}' 
					{(string.IsNullOrEmpty(Year) ? "" : $@" and rate.Yearv={Year}")}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-15
        [JsonType]
        [MustLogin]
        [Description("修改单店毛利率")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult UpdatePLBasShopGrossRate(PLBasShopGrossRateModel.ModifyModel param)
        {
            try
            {
                if (param == null) return Error("参数对象为空");
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (param.list != null)
                {
                    param.list.ToList().ForEach(d =>
                    {
                        d.CompanyID = UserInfo.UserCompanyId;
                        d.ExtrSource = UserInfo.UserExtrSource;
                        d.CheckState = 1;
                    });
                    MergePLBasShopGrossRate(param.list);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 枚举
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public void MergePLBasShopGrossRate(IList<PLBasShopGrossRateModel.NeceModel> list)
        {
            try
            {
                var uid = Guid.NewGuid().ToString().Replace("-", "");
                var tempTableName = $" ngoods.ngoods_{uid} ";
                const string targetTableName = @" ngoods.ngoods_PL_Bas_ShopGrossRate ";
                var dt =
                    DataTableHelper.LinqToDataTable(
                        list.DistinctBy(d => new { d.CompanyID, d.Yearv, d.SeasonID, d.KindID, d.SeriesID }));
                const string onConditions = @" targetTable.CompanyID=sourceTable.CompanyID
         AND targetTable.ExtrSource=sourceTable.ExtrSource
         AND targetTable.Yearv=sourceTable.Yearv
         AND targetTable.SeasonID=sourceTable.SeasonID
         AND targetTable.KindID=sourceTable.KindID
         AND targetTable.SeriesID=sourceTable.SeriesID ";
                const string setConditions = @" GrossRate = sourceTable.GrossRate/100 ,
                                            CheckState = sourceTable.CheckState ,
                                            Remark = sourceTable.Remark ";
                const string insertConditions = @" CompanyID ,
                      ExtrSource ,
                      Yearv ,
                      SeasonID ,
                      KindID ,
                      SeriesID ,
                      GrossRate ,
                      CheckState ,
                      Remark ";
                const string valuesConditions = @" sourceTable.CompanyID ,
                      sourceTable.ExtrSource ,
                      sourceTable.Yearv ,
                      sourceTable.SeasonID ,
                      sourceTable.KindID ,
                      sourceTable.SeriesID ,
                      sourceTable.GrossRate/100 ,
                      sourceTable.CheckState ,
                      sourceTable.Remark ";
                Helper.Merge(tempTableName, targetTableName, dt, onConditions, setConditions, insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-15
        public DataTable ExportPLBasShopGrossRateTemp(string Keys)
        {
            try
            {
                var sql = $@"SELECT  rate.CompanyID ,
        rate.Yearv ,
        season.SeasonName ,
        kind.KindName ,
        series.SeriesName ,
        rate.Remark ,
        rate.GrossRate * 100 AS GrossRate
FROM    ngoods.ngoods_PL_Bas_ShopGrossRate rate
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Kind
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) kind ON rate.KindID = kind.KindID
                             AND rate.ExtrSource = kind.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Series 
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) series ON rate.SeriesID = series.SeriesID
                        AND rate.ExtrSource = series.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Season 
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) season ON rate.SeasonID = season.SeasonID
                        AND rate.ExtrSource = season.ExtrSource
WHERE   rate.CompanyID = '{UserInfo.UserCompanyId}'
        AND rate.ExtrSource = '{UserInfo.UserExtrSource}'
		{(string.IsNullOrEmpty(Keys) ? "" : Keys)}
ORDER BY rate.Yearv DESC ,
        rate.SeasonID DESC ,
        rate.KindID ,
        rate.SeriesID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public void CreateTempTable(DataTable dt, string TempTableName, Dictionary<string, string> Cesd)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    CompanyID varchar(30),
    ExtrSource varchar(30),
    Yearv int,
    SeasonName varchar(120),
    KindName varchar(120),
    SeriesName varchar(180),
    GrossRate numeric(8,4),
    Remark varchar(150)
)";
                dt.Columns.Add("ExtrSource", System.Type.GetType("System.String"));
                foreach (DataRow item in dt.Rows)
                {
                    var companyID = item["CompanyID"].ToString();
                    item["ExtrSource"] = Cesd.Keys.Contains(companyID) ? Cesd[companyID] : "";
                }
                Helper.ExecuteSqlTran(dt, TempTableName, sql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导入单店毛利率
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public DataTable ImportPLBasShopGrossRate(string TempTableName, Dictionary<string, string> Cesd)
        {
            try
            {
                var sql = $@"SELECT  temp.CompanyID ,
        temp.Yearv ,
        temp.SeasonName ,
        temp.KindName ,
        temp.SeriesName ,
        temp.GrossRate ,
        temp.Remark ,
        season.SeasonID ,
        kind.KindID ,
        series.SeriesID
FROM    {TempTableName} temp
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Kind
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) kind ON temp.KindName = kind.KindName
                            AND temp.ExtrSource = kind.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Series 
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) series ON rate.SeriesName = series.SeriesName
                       AND temp.ExtrSource = series.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Season 
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) season ON rate.SeasonName = season.SeasonName
                       AND temp.ExtrSource = season.ExtrSource";
                var companyID = UserInfo.UserCompanyId;
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PLBasShopGrossRateModel.ListModel>.ConvertToModel(dt);
                var resultList = new List<PLBasShopGrossRateModel.ListModel>();
                    //list.Where(
                    //        d =>
                    //            d.CompanyID.Equals(UserInfo.UserCompanyId) && d.SeasonID != null && d.KindID != null &&
                    //            d.SeriesID != null && d.GrossRate >= 0 && d.GrossRate <= 100)
                    //    .Select(d => d)
                    //    .ToList();
                foreach (var item in list)
                {
                    if (item.CompanyID == companyID && Cesd.Keys.Contains(item.CompanyID) &&
                        item.SeasonID != null &&
                        item.KindID != null &&
                        item.SeriesID != null && item.GrossRate >= 0 && item.GrossRate <= 100)
                    {
                        item.ExtrSource = Cesd[item.CompanyID];
                        resultList.Add(item);
                    }
                }
                MergePLBasShopGrossRate(resultList.Select(d => new PLBasShopGrossRateModel.NeceModel
                {
                    CompanyID = d.CompanyID,
                    ExtrSource = d.ExtrSource,
                    Yearv = d.Yearv,
                    SeasonID = d.SeasonID,
                    KindID = d.KindID,
                    SeriesID = d.SeriesID,
                    GrossRate = d.GrossRate,
                    Remark = d.Remark,
                    CheckState = 1L,
                }).ToList());
                var errorList = list.Except(resultList);
                var errorDT = DataTableHelper.LinqToDataTable(errorList);
                //if (errorDT.Rows.Count > 0)
                //{
                //    errorDT.Columns.Remove("SeasonID");
                //    errorDT.Columns.Remove("KindID");
                //    errorDT.Columns.Remove("SeriesID");
                //    errorDT.Columns.Remove("SaleRate");
                //    errorDT.Columns.Remove("BuyRate");
                //    errorDT.Columns.Remove("TotalSaleRate");
                //}
                return errorDT;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
